package drugdealer.persistencia;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

import drugdealer.dados.Remedio;
import drugdealer.dados.Transacao;
import drugdealer.persistencia.exceptions.*;

public class TransacaoPersistence extends TablePersistence implements ITransacaoPersistence {
	public static String TABLENAME = "transacao";
	public static String PRIMARYKEY1 = "codigo";
	public static String PRIMARYKEY2 = "hospital_origem";

	public TransacaoPersistence(DDPersistence owner) {
		super(owner);
		this.tableName = RemedioPersistence.TABLENAME;
		this.primaryKey = RemedioPersistence.PRIMARYKEY;
		this.createTableStmt =
			"CREATE TABLE IF NOT EXISTS " + TransacaoPersistence.TABLENAME + " (" +
			TransacaoPersistence.PRIMARYKEY1 + " integer NOT NULL, " +
			TransacaoPersistence.PRIMARYKEY2 + " varchar(50), " +
			"cod_remedio integer NOT NULL, " +
			"quantidade integer NOT NULL, " +
			"tipo integer NOT NULL, " +
			"situacao integer NOT NULL, " +
			"data_inicio varchar(10), " +
			"data_fim varchar(10), " +
			"PRIMARY KEY (" + 
			TransacaoPersistence.PRIMARYKEY1 + "," +
			TransacaoPersistence.PRIMARYKEY2 + ")," +
			"FOREIGN KEY (cod_remedio) REFERENCES " + 
			RemedioPersistence.TABLENAME + "(" + RemedioPersistence.PRIMARYKEY +
			") ON DELETE CASCADE" +
			")";
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.ITransacaoPersistence#get(int, java.lang.String)
	 */
	public Transacao get(int codigo, String hospitalOrigem) {
		Transacao trans = null;
		String selectLote = "SELECT * FROM " + TransacaoPersistence.TABLENAME + 
				" WHERE " + TransacaoPersistence.PRIMARYKEY1 + "=" + codigo +
				" AND " + TransacaoPersistence.PRIMARYKEY2 + "='" + 
				hospitalOrigem + "'";
		
		try {
			Statement s = this.ddc.createStatement();
			ResultSet rs = s.executeQuery(selectLote);
			if (rs.next()) {
				DateFormat formato = new SimpleDateFormat("dd/MM/yyyy");
				Date dataInicio, dataFim;
				try {
					dataInicio = formato.parse(rs.getString("data_inicio"));
				} catch (ParseException e) {
					dataInicio = null;
				}
				try {
					dataFim = formato.parse(rs.getString("data_fim"));
				} catch (ParseException e) {
					dataFim = null;
				}
				trans = new Transacao(
						rs.getInt(TransacaoPersistence.PRIMARYKEY1),
						rs.getString(TransacaoPersistence.PRIMARYKEY2),
						(Remedio) this.ddc.get(
								DDPersistence.REMEDIO,rs.getInt("cod_remedio")),
						rs.getInt("quantidade"), (rs.getInt("tipo") == 1),
						rs.getInt("situacao"), dataInicio, dataFim);
			}
			rs.close ();
			s.close ();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return trans;
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.ITransacaoPersistence#getTransacoesAbertas(boolean)
	 */
	public Vector<Transacao> getTransacoesAbertas(boolean tipo) {
		Vector<Transacao> list = new Vector<Transacao>();
		String selectList =
			"SELECT * FROM " + TransacaoPersistence.TABLENAME +
			" WHERE situacao < 6 AND tipo=";
		if (tipo)
			selectList += "1";
		else
			selectList += "0";

		System.out.println(selectList);

		try {
			Statement s = this.ddc.createStatement();
			ResultSet rs = s.executeQuery(selectList);
			
			while (rs.next()) {
				DateFormat formato = new SimpleDateFormat("dd/MM/yyyy");
				Date dataInicio, dataFim;
				try {
					dataInicio = formato.parse(rs.getString("data_inicio"));
				} catch (ParseException e) {
					dataInicio = null;
				}
				try {
					dataFim = formato.parse(rs.getString("data_fim"));
				} catch (ParseException e) {
					dataFim = null;
				}
				list.add(new Transacao(
						rs.getInt(TransacaoPersistence.PRIMARYKEY1),
						rs.getString(TransacaoPersistence.PRIMARYKEY2),
						(Remedio) this.ddc.get(
								DDPersistence.REMEDIO,rs.getInt("cod_remedio")),
						rs.getInt("quantidade"), (rs.getInt("tipo") == 1),
						rs.getInt("situacao"), dataInicio, dataFim));
			}
			rs.close ();
			s.close ();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	/* (non-Javadoc)
	 * @see drugdealer.persistencia.ITransacaoPersistence#add(drugdealer.dados.Transacao)
	 */
	public void add(Transacao transacao) throws AlreadyExistsException {
		int tipo = 0;
		if (transacao.getTipo())
			tipo = 1;
		super.add("INSERT INTO " + TransacaoPersistence.TABLENAME +
				" VALUES (" + this.getNovoCodigo() + ",'" + 
				transacao.getHospitalOrigem() + "'," +
				+ transacao.getRemedio().getCodigo() + "," +
				transacao.getQuantidade() + "," + tipo + "," + 
				transacao.getSituacao() + ",'" + 
				transacao.getFormatedDataInicio() + "','" + 
				transacao.getFormatedDataFim() + "')");
	}
	
	/**
	 * 
	 */
	private int getNovoCodigo() {
		int codigo = 0;
		String selectCodigos =
			"SELECT " + TransacaoPersistence.PRIMARYKEY1 + " FROM " + 
			TransacaoPersistence.TABLENAME + " ORDER BY " + 
			TransacaoPersistence.PRIMARYKEY1 + " DESC";
		try {
			Statement s = this.ddc.createStatement();
			ResultSet rs = s.executeQuery(selectCodigos);
			if (rs.next())
				codigo = rs.getInt(TransacaoPersistence.PRIMARYKEY1) + 1;
			rs.close();
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return codigo;
	}

	/* (non-Javadoc)
	 * @see drugdealer.persistencia.ITransacaoPersistence#update(drugdealer.dados.Transacao)
	 */
	public void update(Transacao transacao) throws DoesNotExistsException {
		int tipo = 0;
		if (transacao.getTipo())
			tipo = 1;
		super.update("UPDATE " + TransacaoPersistence.TABLENAME + " SET " +
				TransacaoPersistence.PRIMARYKEY2 + "='" + 
				transacao.getHospitalOrigem() + "'," +
				"cod_remedio=" + transacao.getRemedio().getCodigo() + "," +
				"quantidade=" + transacao.getQuantidade() + "," +
				"tipo=" + tipo + ",situacao=" + transacao.getSituacao() + "," +
				"data_inicio='" + transacao.getFormatedDataInicio() + "'," +
				"data_fim='" + transacao.getFormatedDataFim() + "' WHERE " + 
				TransacaoPersistence.PRIMARYKEY1 + "=" + transacao.getCodigo() +
				" AND " + TransacaoPersistence.PRIMARYKEY2 + "='" + 
				transacao.getHospitalOrigem() + "'");
	}

	/* (non-Javadoc)
	 * @see drugdealer.persistencia.ITransacaoPersistence#remove(drugdealer.dados.Transacao)
	 */
	public void remove(Transacao transacao) {
		super.remove(TransacaoPersistence.TABLENAME,
				TransacaoPersistence.PRIMARYKEY1, transacao.getCodigo(),
				TransacaoPersistence.PRIMARYKEY2, transacao.getHospitalOrigem());
	}
}